﻿using System;
using System.Collections;
using System.Data;
using System.Diagnostics;
using System.IO;
using System.Windows.Forms;
using MySql.Data.MySqlClient;

namespace KantoorDienst
{
    class DBConnect
    {
        #region generic stuff

        private MySqlConnection connection;
        private string server;
        private string database;
        private string uid;
        private string password;

        //Constructor
        public DBConnect()
        {
            Initialize();
        }

        //Initialize values
        private void Initialize()
        {
            server = "195.240.191.229"; //server, lokaal, internet of netwerk
            database = "kantoordienst"; //databasenaam
            uid = "geuzen"; //gebruikersnaam
            password = "geuzen123"; //wachtwoord
            //server = GeuzenAdministratie.Properties.Settings.Default.dbServer; //server, lokaal, internet of netwerk
            //database = GeuzenAdministratie.Properties.Settings.Default.dbName; //databasenaam
            //uid = GeuzenAdministratie.Properties.Settings.Default.dbUser; //gebruikersnaam
            //password = GeuzenAdministratie.Properties.Settings.Default.dbPass; //wachtwoord
            string connectionString;
            connectionString = "SERVER=" + server + ";" + "DATABASE=" +
            database + ";" + "UID=" + uid + ";" + "PASSWORD=" + password + ";";

            connection = new MySqlConnection(connectionString);
        }

        //open connection to database
        private bool OpenConnection()
        {
            try
            {
                connection.Open();
                return true;
            }
            catch (MySqlException ex)
            {
                //When handling errors, you can your application's response based 
                //on the error number.
                //The two most common error numbers when connecting are as follows:
                //0: Cannot connect to server.
                //1045: Invalid user name and/or password.
                switch (ex.Number)
                {
                    case 0:
                        Console.WriteLine("Cannot connect to server.  Contact administrator",30);
                        break;

                    case 1045:
                        Console.WriteLine("Verkeerde gebruikersnaam/wachtwoord database. Contacteer administrator.", 30);
                        break;
                    default:
                        Console.WriteLine("problemen met verbinding database. Bel Marco, 0623506502\nGeef de volgende foutmelding mee:" + ex.Number, 30);
                        break;
                }
                
                return false;
            }
        }

        //Close connection
        private bool CloseConnection()
        {
            try
            {
                connection.Close();
                return true;
            }
            catch (MySqlException ex)
            {
                MessageBox.Show(ex.Message);
                return false;
            }
        }
        #endregion
        #region db Queries
        public DataTable getWeaponsList()
        {
            string query = "SELECT p.name, p.prod_id, p.barcode, p.item_no, p.cal_id FROM Product p WHERE p.type ='Weapon'";
            return SQLDataTable(query);
        }
        public DataTable getCaliberList()
        {
            string query = "SELECT cal_id, name, size FROM Caliber";
            return SQLDataTable(query);
        }
        public DataTable getMunitionList()
        {
            string q = "SELECT `prod_id`, `name`, `barcode`, `type`, `price`, `cal_id`, `item_no`, `stock`, `permit` FROM `Product` WHERE `type`='Munition';";
            Console.WriteLine(q);
            return SQLDataTable(q);
        }
        public DataTable searchBarcode(string s)
        {
            string q = "SELECT p.Prod_id,p.barcode, p.name, p.price, p.cal_id, p.type, p.item_no, p.permit FROM Product p WHERE p.barcode='"+s+"'";
            return SQLDataTable(q);
        }
        public void setWeaponnumber(string number, string prod_id)
        {
            string query = "UPDATE `Product` SET `item_no`='"+number+"' WHERE prod_id="+prod_id;
            SQLNoReturn(query);
        }
        public void setBarcode(string barcode, string prod_id)
        {
            string q = "UPDATE `Product` SET `barcode`='" + barcode + "' WHERE prod_id=" + prod_id;
            SQLNoReturn(q);
        }
        public void setName(string name, string prod_id)
        {
            string q = "UPDATE `Product` SET `name`='" + name + "' WHERE prod_id=" + prod_id;
            SQLNoReturn(q);
        }
        public void newWeapon()
        {
            string q = "INSERT INTO `Product` (`name` ,`barcode` ,`type`)VALUES ( 'Nieuw wapen',  NULL,  'Weapon');";
            SQLNoReturn(q);
        }
        public void newCaliber()
        {
            string q = "INSERT INTO Caliber VALUES(NULL, 'nieuw kaliber', '0');";
            SQLNoReturn(q);
        }
        public void newItem()
        {
            string q = "INSERT INTO `Product` (`name`, `barcode`, `type`, `price`, `cal_id`, `item_no`) VALUES ('naamloos', '0', 'Munition', '0', '', '');";
            SQLNoReturn(q);
        }
        public void setCaliber(string caliber_id, string prod_id)
        {
            string q = "UPDATE `Product` SET `cal_id`='" + caliber_id + "' WHERE prod_id='" + prod_id+"'";
            SQLNoReturn(q);
        }
        public void setCaliberNameSize(string caliber_id, string name, string size)
        {
            string q = "UPDATE `Caliber` SET `name`= '" + name + "',`size`='" + size + "' WHERE cal_id='"+caliber_id+"';";
            SQLNoReturn(q);
        }
        public void setItem(string name, string barcode, string price, string cal_id, string item_no, string prod_id, int permit)
        {
            string q = "UPDATE `Product` SET `name`='" + name + "',`barcode`='" + barcode + "',`price`='" + price + "',`cal_id`='" + cal_id + "',`item_no`='" + item_no + "',`permit`='" + permit + "' WHERE `prod_id` = '" + prod_id + "';";
            Console.WriteLine(q);
            SQLNoReturn(q);
        }
        public void addLog(DataTable dt)
        {
            //add everything in the pList to log in db
            foreach (DataRow dr in dt.Rows)
            {
                try
                { 
                    int prod_id = int.Parse(dr["prod_id"].ToString());
                    int amount = int.Parse(dr["amount"].ToString());
                    decimal itemprice = decimal.Parse(dr["itemprice"].ToString());
                    int knsa = int.Parse(dr["knsa"].ToString());
                    string name = dr["name"].ToString();
                    string cal_name = dr["cal_name"].ToString();
                    string q = "INSERT INTO `Salesrow` (`prod_id`, `amount`, `itemprice`, `knsa`, `name`, `cal_name` ) VALUES ('" + prod_id + "','" + amount + "','" + itemprice+ "','" + knsa+ "','" + name+ "','" + cal_name+ "',);";
                    SQLNoReturn(q);
                }
                catch (Exception)
                {

                }
            }

           
        }

        #endregion
        #region db member queries
        public DataTable getMemberByKnsa(string knsa)
        {
            string query = "SELECT * FROM members WHERE `KNSA` = '" + knsa + "' LIMIT 1";
            return SQLDataTable(query);
        }

        //zien of persoon al aangemeld heeft
        public bool getAanwezigVandaag(string KNSA)
        {
            DataTable DT = new DataTable();
            DateTime date = DateTime.Now;
            string onlydate = date.ToString("yyyy-MM-dd");
            string query = "SELECT presence.checkin FROM presence WHERE DATE(presence.checkin)='" + onlydate + "' AND presence.KNSA='" + KNSA + "'";
            if (this.OpenConnection() == true)
            {
                MySqlDataAdapter DA = new MySqlDataAdapter();
                DA.SelectCommand = new MySqlCommand(query, connection);
                DA.Fill(DT);
                Console.WriteLine(query);
                //close Connection
                this.CloseConnection();
                if (DT.Rows.Count >= 1)
                {
                    return true;
                }
                else { return false; }
            }
            else
            {
                MessageBox.Show("aanwezigheidsregistratie gefaald! - controleer of lid in database staat.");
                return true;
            }
        }
        public void setAanwezig(string knsa)
        {
            string query = "INSERT INTO presence (KNSA) VALUES ('" + knsa+ "')";
            SQLNoReturn(query);
        }

        #endregion

        #region database functions

        /// <summary>
        /// method that uses query q to return a DataTable from the Database
        /// </summary>
        private DataTable SQLDataTable(string q)
        {
            DataTable DT = new DataTable();

            //Open connection
            if (this.OpenConnection() == true)
            {
                MySqlDataAdapter DA = new MySqlDataAdapter();
                DA.SelectCommand = new MySqlCommand(q, connection);
                DA.Fill(DT);

                //close Connection
                this.CloseConnection();

                //return list to be displayed
                return DT;
            }
            else
            {

                MessageBox.Show("Error! geen OpenConnection True!");
                return DT;

                //TODO: GIVE ERROR!!!!
            }
        }

        private void SQLNoReturn(string q)
        {
            //Open connection
            if (this.OpenConnection() == true)
            {
                //create mysql command
                MySqlCommand cmd = new MySqlCommand();
                //Assign the query using CommandText
                cmd.CommandText = q;
                //Assign the connection using Connection
                cmd.Connection = connection;

                //Execute query
                cmd.ExecuteNonQuery();

                //close connection
                this.CloseConnection();
            }
            else
            {
                MessageBox.Show("Error! geen OpenConnection True!");
            }
        }

        //count number of results
        public int Count()
        {
            string query = "SELECT Count(*) FROM tableinfo";
            int Count = -1;

            //Open Connection
            if (this.OpenConnection() == true)
            {
                //Create Mysql Command
                MySqlCommand cmd = new MySqlCommand(query, connection);

                //ExecuteScalar will return one value
                Count = int.Parse(cmd.ExecuteScalar() + "");

                //close Connection
                this.CloseConnection();

                return Count;
            }
            else
            {
                return Count;
            }
        }
        public void Backup(string file)
        {
            string constr = "server="+server+";user="+uid+";pwd="+password+";database="+database+";";
            MySqlBackup mb = new MySqlBackup(constr);
            mb.ExportInfo.FileName = file;
            mb.Export();
        }
        public void Restore(string file)
        {
            string constr = "server=" + server + ";user=" + uid + ";pwd=" + password + ";database=" + database + ";";
            MySqlBackup mb = new MySqlBackup(constr);
            mb.ImportInfo.FileName = file;
            mb.Import();
        }
        #endregion
    }
}
